﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class OmSalesOrderDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public OmSalesOrderDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;

        }
        /// <summary>
        /// 库存查询
        /// </summary>
        /// <param name="goodsCode"></param>
        /// <returns></returns>
        public async Task<List<WhInventory>> GetInventoryByGoodsCode(string goodsCode)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"
                SELECT 
	                wi.inventory_id as InventoryId,
	                wi.warehouse_id as WarehouseId,
	                wi.storage_location_id as StorageLocationId,
	                wi.goods_id as GoodsId,
	                wi.unit_price as UnitPrice,
	                wi.available_inventory_qty as AvailableInventoryQty,
	                wi.total_planned_quantity as TotalPlannedQuantity,
	                w.warehouse_name as WarehouseName,
	                w.warehouse_capacity as WarehouseCapacity,
	                w.warehouse_code as WarehouseCode,
	                wl.storage_location_name as LocationName,
	                wl.storage_location_code as LocationCode,
	                wl.storage_location_capacity as StorageLocationCapacity
                FROM wh_inventory wi
                INNER JOIN [dbo].[bi_warehouse] w ON wi.warehouse_id = w.warehouse_id
                INNER JOIN wh_location wl ON wi.storage_location_id = wl.storage_location_id
                INNER JOIN bi_product bp ON wi.goods_id = bp.goods_id
                WHERE bp.goods_code = @goodsCode 
                AND wi.available_inventory_qty > 0
                ORDER BY wi.available_inventory_qty DESC";

                return (await con.QueryAsync<WhInventory>(sql, new { goodsCode })).ToList();
            }
        }
        /// 获取所有销售订单（优化版）
        /// </summary>
        public async Task<List<SalesOrderDTO>> GetAllWithDetailsAsync()
        {
            using (var con = new SqlConnection(URL))
            {
                string sql = @"
                SELECT 
                    o.sales_order_id as SalesOrderId,
                    o.sales_order_no as SalesOrderNo,
                    c.customer_name as CustomerName,
                    c.contact_person as ContactPerson,
                    c.contact_phone as ContactPhon,
                    sellerUser.users_name as SellerBy,
                    sellerOrg.org_short_name as SellerShortName,
                    o.sales_date as SalesDate,
                    o.order_status as OrderStatus,
                    auditorUser.users_name as AuditorBy,
                    o.auditor_time as AuditorTime,
                    o.auditor_comment as AuditorComment,
                    o.is_delete as IsDelete,
                    createUser.users_true_name as CreateBy,
                    createOrg.org_short_name as CreateShortName,
                    o.create_time as CreateTime,
                    updateUser.users_true_name as UpdateBy,
                    o.update_time as UpdateTime,
                    o.remarks as Remarks,
                    STUFF((
                        SELECT '、' + p.goods_name
                        FROM om_sales_order_detail od
                        INNER JOIN bi_product p ON od.goods_code = p.goods_code
                        WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                        FOR XML PATH('')
                    ), 1, 1, '') as GoodsNamelist,
                    ISNULL((
                        SELECT SUM(od.sales_amount)
                        FROM om_sales_order_detail od
                        WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                    ), 0) as SalesAmount,
                    ISNULL((
                        SELECT SUM(od.order_quantity)
                        FROM om_sales_order_detail od
                        WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                    ), 0) as SalesQuantity
                FROM om_sales_order o
                LEFT JOIN bi_customer c ON o.customer_id = c.customer_id
                LEFT JOIN sys_users sellerUser ON o.seller_by = sellerUser.users_id
                LEFT JOIN sys_organization sellerOrg ON sellerUser.org_id = sellerOrg.org_id
                LEFT JOIN sys_users auditorUser ON o.auditor_by = auditorUser.users_id
                LEFT JOIN sys_users createUser ON o.create_by = createUser.users_id
                LEFT JOIN sys_organization createOrg ON createUser.org_id = createOrg.org_id
                LEFT JOIN sys_users updateUser ON o.update_by = updateUser.users_id
                WHERE o.is_delete = '0'
                ORDER BY o.create_time DESC";

                return (await con.QueryAsync<SalesOrderDTO>(sql)).ToList();
            }
        }

        /// <summary>
        /// 根据id获取所有销售订单（优化版）
        /// </summary>
        public async Task<List<SalesOrderDTO>> GetAllWithDetailsIdAsync(long id)
        {
            using (var con = new SqlConnection(URL))
            {
                string sql = @"
                    SELECT 
                        o.sales_order_id as SalesOrderId,
                        o.sales_order_no as SalesOrderNo,
                        c.customer_name as CustomerName,
                        c.contact_person as ContactPerson,
                        c.contact_phone as ContactPhon,
                        sellerUser.users_name as SellerBy,
                        sellerOrg.org_short_name as SellerShortName,
                        o.sales_date as SalesDate,
                        o.order_status as OrderStatus,
                        auditorUser.users_name as AuditorBy,
                        o.auditor_time as AuditorTime,
                        o.auditor_comment as AuditorComment,
                        o.is_delete as IsDelete,
                        createUser.users_true_name as CreateBy,
                        createOrg.org_short_name as CreateShortName,
                        o.create_time as CreateTime,
                        updateUser.users_true_name as UpdateBy,
                        o.update_time as UpdateTime,
                        o.remarks as Remarks,
                        STUFF((
                            SELECT '、' + p.goods_name
                            FROM om_sales_order_detail od
                            INNER JOIN bi_product p ON od.goods_code = p.goods_code
                            WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                            FOR XML PATH('')
                        ), 1, 1, '') as GoodsNamelist,
                        ISNULL((
                            SELECT SUM(od.sales_amount)
                            FROM om_sales_order_detail od
                            WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                        ), 0) as SalesAmount,
                        ISNULL((
                            SELECT SUM(od.order_quantity)
                            FROM om_sales_order_detail od
                            WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                        ), 0) as SalesQuantity
                    FROM om_sales_order o
                    LEFT JOIN bi_customer c ON o.customer_id = c.customer_id
                    LEFT JOIN sys_users sellerUser ON o.seller_by = sellerUser.users_id
                    LEFT JOIN sys_organization sellerOrg ON sellerUser.org_id = sellerOrg.org_id
                    LEFT JOIN sys_users auditorUser ON o.auditor_by = auditorUser.users_id
                    LEFT JOIN sys_users createUser ON o.create_by = createUser.users_id
                    LEFT JOIN sys_organization createOrg ON createUser.org_id = createOrg.org_id
                    LEFT JOIN sys_users updateUser ON o.update_by = updateUser.users_id
                            WHERE o.is_delete = '0' and  o.sales_order_id=@id
                            ORDER BY o.create_time DESC";

                return (await con.QueryAsync<SalesOrderDTO>(sql, new { id })).ToList();
            }
        }
        /// <summary>
        /// 根据编号获取所有销售订单（优化版）
        /// </summary>
        public async Task<List<SalesOrderDTO>> GetAllWithDetailsIdAsync(string id)
        {
            using (var con = new SqlConnection(URL))
            {
                string sql = @"
                    SELECT 
                        o.sales_order_id as SalesOrderId,
                        o.sales_order_no as SalesOrderNo,
                        c.customer_name as CustomerName,
                        c.contact_person as ContactPerson,
                        c.contact_phone as ContactPhon,
                        sellerUser.users_name as SellerBy,
                        sellerOrg.org_short_name as SellerShortName,
                        o.sales_date as SalesDate,
                        o.order_status as OrderStatus,
                        auditorUser.users_name as AuditorBy,
                        o.auditor_time as AuditorTime,
                        o.auditor_comment as AuditorComment,
                        o.is_delete as IsDelete,
                        createUser.users_true_name as CreateBy,
                        createOrg.org_short_name as CreateShortName,
                        o.create_time as CreateTime,
                        updateUser.users_true_name as UpdateBy,
                        o.update_time as UpdateTime,
                        o.remarks as Remarks,
                        STUFF((
                            SELECT '、' + p.goods_name
                            FROM om_sales_order_detail od
                            INNER JOIN bi_product p ON od.goods_code = p.goods_code
                            WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                            FOR XML PATH('')
                        ), 1, 1, '') as GoodsNamelist,
                        ISNULL((
                            SELECT SUM(od.sales_amount)
                            FROM om_sales_order_detail od
                            WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                        ), 0) as SalesAmount,
                        ISNULL((
                            SELECT SUM(od.order_quantity)
                            FROM om_sales_order_detail od
                            WHERE od.sales_order_id = o.sales_order_id AND od.is_delete = '0'
                        ), 0) as SalesQuantity
                    FROM om_sales_order o
                    LEFT JOIN bi_customer c ON o.customer_id = c.customer_id
                    LEFT JOIN sys_users sellerUser ON o.seller_by = sellerUser.users_id
                    LEFT JOIN sys_organization sellerOrg ON sellerUser.org_id = sellerOrg.org_id
                    LEFT JOIN sys_users auditorUser ON o.auditor_by = auditorUser.users_id
                    LEFT JOIN sys_users createUser ON o.create_by = createUser.users_id
                    LEFT JOIN sys_organization createOrg ON createUser.org_id = createOrg.org_id
                    LEFT JOIN sys_users updateUser ON o.update_by = updateUser.users_id
                            WHERE o.is_delete = '0' and  o.sales_order_no=@id
                            ORDER BY o.create_time DESC";

                return (await con.QueryAsync<SalesOrderDTO>(sql, new { id })).ToList();
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="data">实体</param>
        /// <returns>结果</returns>
        public async Task<int> CreateAsync(OmSalesOrder data)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"
        INSERT INTO [dbo].[om_sales_order] 
        (
            customer_id, 
            sales_order_no, 
            seller_by, 
            sales_date, 
            order_status, 
            auditor_by, 
            auditor_time, 
            auditor_comment, 
            is_delete, 
            create_by, 
            create_time, 
            update_by, 
            update_time, 
            remarks
        )
        VALUES 
        (
            @CustomerId, 
            @SalesOrderNo, 
            @SellerBy, 
            GETDATE(), 
            @OrderStatus, 
            @AuditorBy, 
            GETDATE(), 
            @AuditorComment, 
            @IsDelete, 
            @CreateBy, 
            GETDATE(), 
            @UpdateBy, 
            GETDATE(), 
            @Remarks
        )";
                return await con.ExecuteAsync(sql, data);

            }
        }

        /// <summary>
        /// 根据ID查询
        /// </summary>
        /// <param name="Id">ID</param>
        /// <returns>用户列表</returns>
        public async Task<OmSalesOrder> GetByIdAsync(long Id)
        {
            // 根据ID查询
            return await _dbContext.OmSalesOrder.FirstOrDefaultAsync(g => g.SalesOrderId == Id);
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns>用户列表</returns>
        public async Task<List<OmSalesOrder>> GetAllAsync()
        {
            // 查询所有并转换为列表
            return await _dbContext.OmSalesOrder.ToListAsync();
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="date">包含更新信息</param>
        public async Task<int> UpdateAsync(OmSalesOrder date)
        {
            // 标记实体为修改状态
            _dbContext.OmSalesOrder.Update(date);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }
        /// <summary>
        /// 获取用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<UserOrgInfo> GetUsersId(long id)
        {
            // 根据ID查询
            using (SqlConnection con = new SqlConnection(URL))
            {
                // 建议使用参数化查询，避免SQL注入
                string sql = @"select a.[users_name], b.[org_short_name] 
                       FROM [dbo].[sys_users] a
                       JOIN [dbo].[sys_organization] b ON a.org_id = b.org_id
                       where a.[users_id] = @Id";
                return await con.QueryFirstOrDefaultAsync<UserOrgInfo>(sql, new { id });
            }
        }
        public async Task<List<WhInventory>> GetInventoryId(string code)
        {

            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @$"EXEC [dbo].[usp_get_inventory_by_goods_code] @GoodsCode = @code";
                return (await con.QueryAsync<WhInventory>(sql, new { code })).ToList();
            }
        }
        /*
        /// <summary>
        /// 根据ID逻辑删除订单及明细（更新是否删除状态）
        /// </summary>
        /// <param name="Id">订单ID</param>
        public async Task<int> DeleteAsync(long Id)
        {
            using var transaction = await _dbContext.Database.BeginTransactionAsync();

            try
            {
                // 更新明细的删除状态
                var details = _dbContext.OmSalesOrderDetail
                    .Where(x => x.SalesOrderId == Id);

                foreach (var detail in details)
                {
                    detail.IsDelete = "1"; //  "1" 表示已删除
                }

                // 更新订单的删除状态
                var order = await _dbContext.OmSalesOrder.FindAsync(Id);
                if (order != null)
                {
                    order.IsDelete = "1"; //  "1" 表示已删除
                    order.UpdateTime = DateTime.Now; // 更新修改时间

                    var result = await _dbContext.SaveChangesAsync();
                    await transaction.CommitAsync();
                    return result;
                }

                await transaction.CommitAsync();
                return 0;
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }
        */
        /// <summary>
        /// 根据ID删除订单及明细
        /// </summary>
        /// <param name="Id">订单ID</param>
        public async Task<int> DeleteAsync(long Id)
        {
            using var transaction = await _dbContext.Database.BeginTransactionAsync();

            try
            {
                // 先执行明细删除的 SQL
                var detailDeleteSql = $"DELETE FROM [dbo].[om_sales_order_detail] WHERE  [sales_order_id]= {Id}";
                await _dbContext.Database.ExecuteSqlRawAsync(detailDeleteSql);

                // 再执行订单删除的 SQL
                var orderDeleteSql = $"DELETE FROM [dbo].[om_sales_order] WHERE [sales_order_id]= {Id}";
                var result = await _dbContext.Database.ExecuteSqlRawAsync(orderDeleteSql);

                await transaction.CommitAsync();
                return result;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                Console.WriteLine($"删除订单时发生错误: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public async Task<int> UpdateAuditorComment(Auditor date)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "UPDATE [dbo].[om_sales_order] SET [auditor_by]=@AuditorBy,[auditor_time]=@AuditorTime,[auditor_comment]=@AuditorComment,[order_status]=@OrderStatus WHERE [sales_order_id]=@Id  ";
                return await con.ExecuteAsync(sql, date);
            }
        }
        /// <summary>
        /// 状态更改
        /// </summary>
        /// <returns></returns>
        public async Task<int> AuditorStatus(string status, long id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "UPDATE [dbo].[om_sales_order] SET [order_status]=@status WHERE [sales_order_id]=@id";
                return await con.ExecuteAsync(sql, new { status, id });
            }
        }
        /// <summary>
        /// 状态更改(启用停用)
        /// </summary>
        /// <returns></returns>
        public async Task<int> Status(string status, long id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "UPDATE [dbo].[om_sales_order] SET [is_delete]=@status WHERE [sales_order_id]=@id";
                return await con.ExecuteAsync(sql, new { status, id });
            }
        }
    }
}
